# backend/app/services/dashboard_service.py
from sqlalchemy.orm import Session
from sqlalchemy import func, desc
from datetime import datetime, timedelta
import calendar

from backend.app.database.models import Requirement, TestCase, Activity, Statistic


def get_dashboard_stats(db: Session):
    """获取仪表盘统计数据"""
    # 获取当前月份和上个月份
    today = datetime.utcnow()
    current_month_start = datetime(today.year, today.month, 1)

    # 计算上个月的开始日期
    if today.month == 1:
        prev_month_start = datetime(today.year - 1, 12, 1)
    else:
        prev_month_start = datetime(today.year, today.month - 1, 1)

    # 获取当月测试用例数量
    current_month_test_cases = db.query(func.count(TestCase.id)).filter(
        TestCase.created_at >= current_month_start
    ).scalar() or 0

    # 获取上月测试用例数量
    prev_month_test_cases = db.query(func.count(TestCase.id)).filter(
        TestCase.created_at >= prev_month_start,
        TestCase.created_at < current_month_start
    ).scalar() or 0

    # 计算测试用例增长率
    test_cases_growth = 0
    if prev_month_test_cases > 0:
        test_cases_growth = ((current_month_test_cases - prev_month_test_cases) / prev_month_test_cases) * 100

    # 获取当月需求优化数量
    current_month_requirements = db.query(func.count(Requirement.id)).filter(
        Requirement.created_at >= current_month_start
    ).scalar() or 0

    # 获取上月需求优化数量
    prev_month_requirements = db.query(func.count(Requirement.id)).filter(
        Requirement.created_at >= prev_month_start,
        Requirement.created_at < current_month_start
    ).scalar() or 0

    # 计算需求优化增长率
    requirements_growth = 0
    if prev_month_requirements > 0:
        requirements_growth = ((current_month_requirements - prev_month_requirements) / prev_month_requirements) * 100

    # 获取当月平均处理时间
    current_month_avg_time = db.query(func.avg(
        func.coalesce(Requirement.processing_time, 0) + func.coalesce(TestCase.processing_time, 0)
    )).filter(
        (Requirement.created_at >= current_month_start) | (TestCase.created_at >= current_month_start)
    ).scalar() or 0

    # 获取上月平均处理时间
    prev_month_avg_time = db.query(func.avg(
        func.coalesce(Requirement.processing_time, 0) + func.coalesce(TestCase.processing_time, 0)
    )).filter(
        (Requirement.created_at >= prev_month_start) & (Requirement.created_at < current_month_start) |
        (TestCase.created_at >= prev_month_start) & (TestCase.created_at < current_month_start)
    ).scalar() or 0

    # 计算平均处理时间变化
    avg_time_change = 0
    if prev_month_avg_time > 0:
        avg_time_change = current_month_avg_time - prev_month_avg_time

    return {
        "test_cases": {
            "count": current_month_test_cases,
            "growth": round(test_cases_growth, 1)
        },
        "requirements": {
            "count": current_month_requirements,
            "growth": round(requirements_growth, 1)
        },
        "avg_processing_time": {
            "time": round(current_month_avg_time, 1),
            "change": round(avg_time_change, 1)
        }
    }


def get_recent_activities(db: Session, limit: int = 10):
    """获取最近活动"""
    activities = db.query(Activity).order_by(desc(Activity.created_at)).limit(limit).all()

    result = []
    for activity in activities:
        result.append({
            "id": activity.id,
            "user_id": activity.user_id,
            "type": activity.activity_type,
            "description": activity.description,
            "created_at": activity.created_at.strftime("%Y-%m-%d %H:%M:%S")
        })

    return result


def update_daily_statistics(db: Session):
    """更新每日统计数据"""
    today = datetime.utcnow().date()

    # 检查今天的统计是否已存在
    existing_stat = db.query(Statistic).filter(
        func.date(Statistic.date) == today
    ).first()

    if existing_stat:
        return  # 今天的统计已存在，不重复更新

    # 计算今天的统计数据
    today_start = datetime.combine(today, datetime.min.time())
    today_end = datetime.combine(today, datetime.max.time())

    requirements_count = db.query(func.count(Requirement.id)).filter(
        Requirement.created_at >= today_start,
        Requirement.created_at <= today_end
    ).scalar() or 0

    test_cases_count = db.query(func.count(TestCase.id)).filter(
        TestCase.created_at >= today_start,
        TestCase.created_at <= today_end
    ).scalar() or 0

    avg_processing_time = db.query(func.avg(
        func.coalesce(Requirement.processing_time, 0) + func.coalesce(TestCase.processing_time, 0)
    )).filter(
        (Requirement.created_at >= today_start) & (Requirement.created_at <= today_end) |
        (TestCase.created_at >= today_start) & (TestCase.created_at <= today_end)
    ).scalar() or 0

    # 创建新的统计记录
    new_stat = Statistic(
        date=today_start,
        requirements_count=requirements_count,
        test_cases_count=test_cases_count,
        avg_processing_time=avg_processing_time
    )

    db.add(new_stat)
    db.commit()
